package com.ysd.resourceusestatistics.util;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import com.ysd.resourceusestatistics.model.Member;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;

/**
 * ExportExcelUtil
 */
public class ExportExcelUtil {

	public List<Member> exportExcelFile(List<Member> list, String path) {
		try {
			// 第一步，创建一个webbook，对应一个Excel文件
			HSSFWorkbook workbook = new HSSFWorkbook();
			// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
			HSSFSheet sheet = workbook.createSheet("Sheet1");
			// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
			HSSFRow row = sheet.createRow((int) 0);
			// 第四步，创建单元格，并设置值表头 设置表头居中
			HSSFCellStyle style = workbook.createCellStyle();

			sheet.setColumnWidth(0, 3766);
			sheet.setColumnWidth(1, 4766);
			sheet.setColumnWidth(2, 3766);
			sheet.setColumnWidth(3, 3766);
			sheet.setColumnWidth(4, 3766);

			// 设置这些样式
			style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
			style.setFillForegroundColor(IndexedColors.YELLOW.index);
			style.setVerticalAlignment(VerticalAlignment.CENTER);
			style.setBorderBottom(BorderStyle.THIN);
			style.setBorderLeft(BorderStyle.THIN);
			style.setBorderRight(BorderStyle.THIN);
			style.setBorderTop(BorderStyle.THIN);
			// 生成一个字体
			HSSFFont font = workbook.createFont();
			font.setColor(IndexedColors.VIOLET.index);
			font.setFontHeightInPoints((short) 14);
			font.setColor((short) 160);
			font.setFontName("宋体");
			// font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
			// 把字体应用到当前的样式
			style.setFont(font);
			// 生成并设置另一个样式
			HSSFCellStyle style2 = workbook.createCellStyle();
			style2.setAlignment(HorizontalAlignment.CENTER);
			style2.setVerticalAlignment(VerticalAlignment.CENTER);
			// 生成另一个字体
			HSSFFont font2 = workbook.createFont();
			font2.setColor(IndexedColors.VIOLET.index);
			font2.setFontHeightInPoints((short) 12);
			font2.setColor((short) 160);
			font2.setFontName("宋体");
			// 把字体应用到当前的样式
			style2.setFont(font2);

			HSSFCell cell = row.createCell(0);
			cell.setCellValue("id");
			cell.setCellStyle(style);

			cell = row.createCell(1);
			cell.setCellValue("卡号");
			cell.setCellStyle(style);

			cell = row.createCell(2);
			cell.setCellValue("姓名");
			cell.setCellStyle(style);

			cell = row.createCell(3);
			cell.setCellValue("性别");
			cell.setCellStyle(style);

			cell = row.createCell(4);
			cell.setCellValue("身份");
			cell.setCellStyle(style);

			cell = row.createCell(5);
			cell.setCellValue("身份证号");
			cell.setCellStyle(style);

			cell = row.createCell(6);
			cell.setCellValue("系别");
			cell.setCellStyle(style);

			cell = row.createCell(7);
			cell.setCellValue("专业");
			cell.setCellStyle(style);

			cell = row.createCell(8);
			cell.setCellValue("年级");
			cell.setCellStyle(style);

			cell = row.createCell(9);
			cell.setCellValue("手机号");
			cell.setCellStyle(style);

			cell = row.createCell(10);
			cell.setCellValue("入会时间");
			cell.setCellStyle(style);

			cell = row.createCell(11);
			cell.setCellValue("状态");
			cell.setCellStyle(style);

			cell = row.createCell(12);
			cell.setCellValue("备注");
			cell.setCellStyle(style);

			// 第五步，写入实体数据 实际应用中这些数据从数据库得到，
			if (list != null && list.size() > 0) {
				for (int i = 0; i < list.size(); i++) {
					row = sheet.createRow(i + 1);
					Member mList = list.get(i);
					// 第四步，创建单元格，并设置值
					// 添加id信息到excel单元格中--------------
					cell = row.createCell(0);
					cell.setCellValue(mList.getId());
					// 设置该单元格样式
					cell.setCellStyle(style2);
					// --------------------------------------

					// 添加卡号到excel单元格中------------
					cell = row.createCell(1);
					cell.setCellValue(mList.getCardNo());
					// 设置该单元格样式
					cell.setCellStyle(style2);
					// --------------------------------------

					// 添加姓名信息到excel单元格中------------
					cell = row.createCell(2);
					cell.setCellValue(mList.getName());
					// 设置该单元格样式
					cell.setCellStyle(style2);
					// --------------------------------------

					// 添加姓名信息到excel单元格中------------
					cell = row.createCell(3);
					cell.setCellValue(mList.getSex());
					// 设置该单元格样式
					cell.setCellStyle(style2);
					// --------------------------------------

					// 添加身份信息到excel单元格中------------
					cell = row.createCell(4);
					cell.setCellValue(mList.getCategory());
					// 设置该单元格样式
					cell.setCellStyle(style2);
					// --------------------------------------

					// 添加身份证号码信息到excel单元格中------------
					cell = row.createCell(5);
					cell.setCellValue(mList.getCard());
					// 设置该单元格样式
					cell.setCellStyle(style2);
					// --------------------------------------

					// 添加系别信息到excel单元格中------------
					cell = row.createCell(6);
					cell.setCellValue(mList.getDepartment());
					// 设置该单元格样式
					cell.setCellStyle(style2);
					// --------------------------------------

					// 添加专业信息到excel单元格中------------
					cell = row.createCell(7);
					cell.setCellValue(mList.getProfession());
					// 设置该单元格样式
					cell.setCellStyle(style2);
					// --------------------------------------

					// 添加年级信息到excel单元格中------------
					cell = row.createCell(8);
					cell.setCellValue(mList.getGrade());
					// 设置该单元格样式
					cell.setCellStyle(style2);
					// --------------------------------------

					// 添加手机信息息到excel单元格中------------
					cell = row.createCell(9);
					cell.setCellValue(mList.getPhone());
					// 设置该单元格样式
					cell.setCellStyle(style2);
					// --------------------------------------

					// 添加入会信息信息到excel单元格中---------
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
					String str = sdf.format(mList.getRegdate());
					cell = row.createCell(10);
					cell.setCellValue(str);
					// 设置该单元格样式
					cell.setCellStyle(style2);
					// --------------------------------------

					// 添加状态信息到excel单元格中------------
					cell = row.createCell(11);
					cell.setCellValue(mList.getStatus());
					// 设置该单元格样式
					cell.setCellStyle(style2);
					// --------------------------------------

					// 添加备注信息到excel单元格中---------
					cell = row.createCell(12);
					cell.setCellValue(mList.getRemark());
					// 设置该单元格样式
					cell.setCellStyle(style2);
					// --------------------------------------

				}

				workbook.close();
			}
			// 第六步，将文件存到指定位置
			FileOutputStream fout = new FileOutputStream(
					path + "/检查信息备份文件" + new SimpleDateFormat("yyyy-MM-dd").format(new Date()) + ".xls");
			workbook.write(fout);
			fout.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;

	}

	public static void exprotExcelFile(List<Member> list, String string, String string2, Class<Member> class1,
			String string3) {
	}

	public static void exportExcelFile(List<Member> list, String string, String string2, Class<Member> class1,
			String string3) {
	}

}